package com.enfo.zjabank.dao;

import com.alibaba.fastjson.JSON;
import com.enfo.zjabank.model.Pager;
import com.enfo.zjabank.tool.StringUtil;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhoum on 2016/8/9.
 */
@Repository
public class GridDaoImpl extends BaseDao implements GridDao{

    private static final Integer[][] Integer = null;
	@Override
    public String listByPage(String gridPager,String sql) {
        //映射Pager对象
        Pager pager = JSON.parseObject(gridPager, Pager.class);
		List<Map<String,String>> columns = pager.getColumns();
		System.out.println(columns);
		StringBuilder selectColumns = new StringBuilder();
		for(Map<String,String> map:columns){
			selectColumns.append(","+map.get("id"));
		}
		Map<String, Object> conditionMap = pager.getFastQueryParameters();
		StringBuilder conditionSql = new StringBuilder("1=1");
		for(Map.Entry<String,Object> entry:conditionMap.entrySet()){
			if(entry.getValue()!=null && !StringUtil.isEmpty((String) entry.getValue())){
				conditionSql.append(" and "+entry.getKey()+"='"+entry.getValue()+"'");
			}
		}
		String listSql = "select "+selectColumns.toString().substring(1)+" from user where "+conditionSql+" limit "+pager.getStartRecord()+","+pager.getPageSize();
        String countSql = "select count(1) from user where "+conditionSql;
        //Object[] params = new Object[]{};
        List<Map<String,Object>> result = this.jdbcTemplate.queryForList(listSql.toString());
        int recordCount = this.jdbcTemplate.queryForObject(countSql, Integer.class);
        //页容量
        int pageSize = pager.getPageSize();
        //页数
        pager.setPageCount(recordCount / pageSize + (recordCount % pageSize > 0 ? 1 : 0));
        //记录数
        pager.setRecordCount(recordCount);
        //记录
        pager.setExhibitDatas(result);
        //成功
        pager.setIsSuccess(true);

        String json = JSON.toJSONString(pager);

        System.out.println("====测试用例====="+json);
        return json;
    }
	//折线图
	//列表
    @Override
    public String listShuJu(String gridPager,String sql) {
    	Calendar cal = Calendar.getInstance();
    	//int year = cal.get(Calendar.YEAR);//获取当前年份
    	int month = cal.get(Calendar.MONTH) + 1;//获取当前月份
    	Map<String,BigDecimal[]> map = new HashMap<>();
    	map=listLjbbShuJu();
    	String ssql="";
    	for (Map.Entry<String, BigDecimal[]> entry : map.entrySet()) {
    		 System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
    		 if("".equals(ssql)){
    		 ssql="select '"+entry.getKey()+"' as shijian,'"+entry.getValue()[0] +"' as geren,"+entry.getValue()[1]+" as qiye,"+entry.getValue()[2]+" as gerencount,"+entry.getValue()[3]+" as qiyecount ";
    		 }else{
    		 ssql=ssql+" union all "+"select '"+entry.getKey()+"' as shijian,'"+entry.getValue()[0] +"' as geren,"+entry.getValue()[1]+" as qiye,"+entry.getValue()[2]+" as gerencount,"+entry.getValue()[3]+" as qiyecount ";
    		 }
    	}

        //映射Pager对象
        Pager pager = JSON.parseObject(gridPager, Pager.class);
        //StringBuilder listSql = new StringBuilder("select  * from buz_escape_debts where report_state=1040 and cancel_state<>1040 and input_time like '"+year+"%'");
        StringBuilder listSql = new StringBuilder(ssql);
        listSql.append(" limit "+pager.getStartRecord()+","+pager.getPageSize());
        //String countSql = "select count(1) from buz_escape_debts where report_state=1040 and cancel_state<>1040 and input_time like '"+year+"%'";
        //Object[] params = new Object[]{};
        List<Map<String,Object>> result = this.jdbcTemplate.queryForList(listSql.toString());
        //int recordCount = this.jdbcTemplate.queryForObject(countSql, Integer.class);
        int recordCount=month;
        //页容量
        int pageSize = pager.getPageSize();
        //页数
        pager.setPageCount(recordCount / pageSize + (recordCount % pageSize > 0 ? 1 : 0));
        //记录数
        pager.setRecordCount(recordCount);
        //记录
        pager.setExhibitDatas(result);
        //成功
        pager.setIsSuccess(true);

        String json = JSON.toJSONString(pager);

        System.out.println("====返回的数据====="+json);
        return json;
    }
    //图表
    @Override
    public   Map<String,BigDecimal[]> listLjbbShuJu(){
    	Calendar cal = Calendar.getInstance();
    	int year = cal.get(Calendar.YEAR);//获取当前年份
    	int month = cal.get(Calendar.MONTH) + 1;//获取当前月份
        //映射Pager对象
    	//String area_code="330100";
        //String listSql = "select distinct  left(input_time, 7) as month from buz_escape_debts where report_state='1040' and cancel_state<>'1040'  and input_time like '"+year+"%'";
        //List<Map<String,Object>> list = this.jdbcTemplate.queryForList(listSql.toString());
        Map<String,BigDecimal[]> map = new HashMap<>();
        for(int i=1;i<=month;i++){
        	String time;
            if(i<10){
            	time=year+"-0"+i;
            }else{
            	time=year+"-"+i;
            }
            BigDecimal[] a=new BigDecimal[4];
            String sql1;
            String sql2;
        	sql1="select count(1) from buz_escape_debts where  report_state=1050  and escaper_type=1 and input_time like '"+time+"%'";
        	a[0] = this.jdbcTemplate.queryForObject(sql1, BigDecimal.class);
        	sql2="select count(1) from buz_escape_debts where  report_state=1050  and escaper_type=2 and input_time like '"+time+"%'";
        	a[1] = this.jdbcTemplate.queryForObject(sql2, BigDecimal.class);
        	String sql3="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   escaper_type=1 and input_time like '"+time+"%')";
        	a[2] = this.jdbcTemplate.queryForObject(sql3, BigDecimal.class);
        	String sql4="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   escaper_type=2 and input_time like '"+time+"%')";
        	a[3] = this.jdbcTemplate.queryForObject(sql4, BigDecimal.class);
        	System.out.println("====测试用例a[2]====="+a[2]);
        	System.out.println("====测试用例a[3]====="+a[3]);
        	map.put(time, a);
        }
        System.out.println("====测试用例====="+map);
        return map;
    }

    //柱状图
    //图表
    public   Map<String,BigDecimal[]> listZhuZhuangShuJu(){
    	Calendar cal = Calendar.getInstance();
    	int year = cal.get(Calendar.YEAR);//获取当前年份
    	int month = cal.get(Calendar.MONTH) + 1;//获取当前月份
        //映射Pager对象
    	//String area_code="330100";
        String listSql = "select a.area_code, b.dept_name from ((select area_code from buz_escape_debts   group by area_code) a left join (select dept_code,dept_name from org_dept group by dept_code,dept_name) b on a.area_code=b.dept_code)  ";
        List<Map<String,Object>> list = this.jdbcTemplate.queryForList(listSql.toString());
        Map<String,BigDecimal[]> map = new HashMap<>();
        if(list.size()>0){
        for(int i=0;i<list.size();i++){
        	String code=(String) list.get(i).get("area_code");
        	String area=(String) list.get(i).get("dept_name");
        	BigDecimal[] a=new BigDecimal[6];
            //未提交
            String sql1="select count(1) from  buz_escape_debts where (report_state=1010 or report_state=1020) and area_code='"+code+"'";
        	a[0] = this.jdbcTemplate.queryForObject(sql1, BigDecimal.class);
        	//已撤销
        	String sql2="select count(1) from  buz_escape_debts where cancel_state=1040 and area_code='"+code+"'";
        	a[1] = this.jdbcTemplate.queryForObject(sql2, BigDecimal.class);
        	//已发布
        	String sql3="select count(1) from  buz_escape_debts where report_state=1050 and  cancel_state<>1040 and area_code='"+code+"'";
        	a[2] = this.jdbcTemplate.queryForObject(sql3, BigDecimal.class);
        	//未提交金额
        	String sql4="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   (report_state=1010 or report_state=1020) and area_code='"+code+"')";
        	a[3] = this.jdbcTemplate.queryForObject(sql4, BigDecimal.class);
        	//已撤销金额
        	String sql5="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   cancel_state=1040 and area_code='"+code+"')";
        	a[4] = this.jdbcTemplate.queryForObject(sql5, BigDecimal.class);
        	//已发布金额
        	String sql6="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   report_state=1050 and area_code='"+code+"')";
        	a[5] = this.jdbcTemplate.queryForObject(sql6, BigDecimal.class);
        	map.put(area, a);
        }
        }
        System.out.println("====测试用例====="+map);
        return map;
    }
    //列表
    @Override
    public String listZhuzhuangLb(String gridPager,String sql) {
    	Map<String,BigDecimal[]> map = new HashMap<>();
    	map=listZhuZhuangShuJu();
    	String ssql="";
    	for (Map.Entry<String, BigDecimal[]> entry : map.entrySet()) {
    		 System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
    		 String area_code=this.jdbcTemplate.queryForObject("select dept_code from org_dept where dept_name='"+entry.getKey()+"'", String.class);
    		 if("".equals(ssql)){
    		 ssql="select '"+area_code+"' as area_code,'"+entry.getValue()[0] +"' as weitijiao,"+entry.getValue()[3]+" as weitijiaoSum,"+entry.getValue()[1]+" as yicexiao,"+entry.getValue()[4]+" as yicexiaoSum, "+entry.getValue()[2]+" as yifabu,"+entry.getValue()[5]+" as yifabuSum ";
    		 }else{
    		 ssql=ssql+" union all "+"select '"+area_code+"' as area_code,'"+entry.getValue()[0] +"' as weitijiao,"+entry.getValue()[3]+" as weitijiaoSum,"+entry.getValue()[1]+" as yicexiao,"+entry.getValue()[4]+" as yicexiaoSum, "+entry.getValue()[2]+" as yifabu,"+entry.getValue()[5]+" as yifabuSum ";
    		 }
    	}
    	//String  Sql = "select a.area_code as area_code,b.weitijiao as tijiao,c.weishenhe as shenhe,d.fabu as fabu  from ((select area_code from buz_escape_debts  group by area_code) a left join (select area_code,count(1) as weitijiao from  buz_escape_debts where (report_state=1010 or report_state=1020)  group by area_code) b on a.area_code=b.area_code left join (select area_code,count(1) as weishenhe from  buz_escape_debts where report_state=1030  group by area_code) c on a.area_code=c.area_code left join (select area_code,count(1) as fabu from  buz_escape_debts where report_state=1050 and  cancel_state<>1040  group by area_code) d on a.area_code=d.area_code)";
        //映射Pager对象
        Pager pager = JSON.parseObject(gridPager, Pager.class);
        //StringBuilder listSql = new StringBuilder("select  * from buz_escape_debts where report_state=1040 and cancel_state<>1040 and input_time like '"+year+"%'");
        StringBuilder listSql = new StringBuilder(ssql);
        listSql.append(" limit "+pager.getStartRecord()+","+pager.getPageSize());
        String countSql = "select count(a.area_code) from ( select distinct area_code  from buz_escape_debts ) a ";
        Object[] params = new Object[]{};
        List<Map<String,Object>> result = this.jdbcTemplate.queryForList(listSql.toString());
        int recordCount = this.jdbcTemplate.queryForObject(countSql, Integer.class);
        //页容量
        int pageSize = pager.getPageSize();
        //页数
        pager.setPageCount(recordCount / pageSize + (recordCount % pageSize > 0 ? 1 : 0));
        //记录数
        pager.setRecordCount(recordCount);
        //记录
        pager.setExhibitDatas(result);
        //成功
        pager.setIsSuccess(true);

        String json = JSON.toJSONString(pager);

        System.out.println("====测试用例====="+json);
        return json;
    }
    ///所有任务统计分析
    //列表
    @Override
    public String listTjfxLb(String gridPager,String sql) {
    	BigDecimal[] a =new BigDecimal[10];
    	a=listTjfxShuJu();
    	String  Sql="";
    	for(int i=0;i<5;i++){
    		if("".equals(Sql)){
    			Sql="select '"+i+"' as lieming,"+a[i]+" as count,"+a[i+5]+" as sum ";
    		}else{
    			Sql=Sql+" union all select '"+i+"' as lieming,"+a[i]+" as count,"+a[i+5]+" as sum ";
    		}
    	}
    	//String  Sql = "select (select count(1) from  buz_escape_debts where report_state=1050  and  cancel_state<>1040 and modify_state<>1040) as fabu,(select count(1) from  buz_escape_debts where cancel_state=1040) as chexiao,(select count(1) from  buz_escape_debts where report_state=1030)  as shenhe,(select count(1) from  buz_escape_debts where report_state=1010)  as jingshiqi,(select count(1) from  buz_escape_debts where modify_state=1040)  as xiugai,(select count(1) from  buz_escape_debts where report_state in (1020,1040,1060,1070)) as qita";

    	//映射Pager对象
        Pager pager = JSON.parseObject(gridPager, Pager.class);
        //StringBuilder listSql = new StringBuilder("select  * from buz_escape_debts where report_state=1040 and cancel_state<>1040 and input_time like '"+year+"%'");
        StringBuilder listSql = new StringBuilder(Sql);
        listSql.append(" limit "+pager.getStartRecord()+","+pager.getPageSize());
        //String countSql = "select count(a.area_code) from ( select distinct area_code  from buz_escape_debts ) a ";
        //Object[] params = new Object[]{};
        List<Map<String,Object>> result = this.jdbcTemplate.queryForList(listSql.toString());
        //int recordCount = this.jdbcTemplate.queryForObject(countSql, Integer.class);
        int recordCount =5;
        //页容量
        int pageSize = pager.getPageSize();
        //页数
        pager.setPageCount(recordCount / pageSize + (recordCount % pageSize > 0 ? 1 : 0));
        //记录数
        pager.setRecordCount(recordCount);
        //记录
        pager.setExhibitDatas(result);
        //成功
        pager.setIsSuccess(true);

        String json = JSON.toJSONString(pager);

        System.out.println("====测试用例====="+json);
        return json;
    }
    //图表
    @Override
    public   BigDecimal[] listTjfxShuJu(){
    	BigDecimal[] a=new BigDecimal[10];
    	    //已发布
            String sql1="select count(1) from  buz_escape_debts where report_state=1050 ";//and  cancel_state<>1040 and modify_state<>1040
        	a[0] = this.jdbcTemplate.queryForObject(sql1, BigDecimal.class);
        	//已撤销
        	String sql2="select count(1) from  buz_escape_debts where cancel_state=1040 ";
        	a[1] = this.jdbcTemplate.queryForObject(sql2, BigDecimal.class);
        	//未审核
        	//String sql3="select count(1) from  buz_escape_debts where report_state=1030";
        	//a[2] = this.jdbcTemplate.queryForObject(sql3, BigDecimal.class);
        	//警示期
        	String sql4="select count(1) from  buz_escape_debts where report_state=1010 ";
        	a[2] = this.jdbcTemplate.queryForObject(sql4, BigDecimal.class);
        	//已修改
        	String sql5="select count(1) from  buz_escape_debts where modify_state=1040  ";
        	a[3] = this.jdbcTemplate.queryForObject(sql5, BigDecimal.class);
        	//其他
        	String sql6="select count(1) from  buz_escape_debts where report_state in (1020,1030,1040,1060,1070)";
        	a[4] = this.jdbcTemplate.queryForObject(sql6, BigDecimal.class);
        	//已发布金额
        	String sql7="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   report_state=1050 )";
        	a[5] = this.jdbcTemplate.queryForObject(sql7, BigDecimal.class);
        	//已撤销金额
        	String sql8="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   cancel_state=1040 )";
        	a[6] = this.jdbcTemplate.queryForObject(sql8, BigDecimal.class);
        	//未审核金额
        	//String sql9="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   report_state=1030 )";
        	//a[8] = this.jdbcTemplate.queryForObject(sql9, BigDecimal.class);
        	//警示期金额
        	String sql10="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   report_state=1010 )";
        	a[7] = this.jdbcTemplate.queryForObject(sql10, BigDecimal.class);
        	//已修改金额
        	String sql11="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   modify_state=1040 )";
        	a[8] = this.jdbcTemplate.queryForObject(sql11, BigDecimal.class);
        	////其他金额
        	String sql12="select case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end from buz_escape_debtInfo where fdebt_id in (select debt_id from buz_escape_debts where   report_state in (1020,1030,1040,1060,1070) )";
        	a[9] = this.jdbcTemplate.queryForObject(sql12, BigDecimal.class);
        return a;
    }


    public String listGytjLb(String params,String gridPager,String sql) {
        //映射Pager对象
    	String[] a=new String[8];
    	a=params.split("\\|");
        Pager pager = JSON.parseObject(gridPager, Pager.class);
        String use="";
        String use1="";
        String sql1="";
        if("1".equals(a[1])){//按照会员统计
        	use="input_dept";
        	use1="org_code";
        	sql1="select * from (select org_code,org_name from  org_manage where org_type=1020  group by org_code,org_name) org ";
        }else if("2".equals(a[1])){//按照会员性质统计
        	use="input_dept";
        	use1="org_code";
        	sql1=" select org_type,org_code from org_manage group by org_type,org_code";
        }else if("3".equals(a[1])){//按照会员所在地区统计
        	use="input_dept";
        	use1="org_code";
        	sql1=" select area_code,org_code from org_manage group by area_code,org_code";
        }else if("4".equals(a[1])){//按照当事人所在地区统计
        	use="area_code";
        	use1="area_code";
        	sql1=" select * from (select area_code from buz_escape_debts group by  area_code) org";
        }else if("5".equals(a[1])){//按照月份统计
        	use="month(input_time)";
        	use1="month(input_time)";
        	sql1="select * from (select month(input_time) from  buz_escape_debts group by  month(input_time)) org";
        }else if("6".equals(a[1])){//按照季度统计
        	use="quarter(input_time)";
        	use1="quarter(input_time)";
        	sql1="select * from (select quarter(input_time) from  buz_escape_debts group by  quarter(input_time)) org";
        }else if("7".equals(a[1])){//按照年统计
        	use="year(input_time)";
        	use1="year(input_time)";
        	sql1="select * from (select year(input_time) from  buz_escape_debts group by  year(input_time)) org";
        }
        String where="";
        if("".equals(a[2])){
        	if(!"".equals(a[3])){
        		where=where +" and area_code="+a[3];
        	}
        	if(!"".equals(a[4])){
        		where=where+" and escaper_class="+a[4];
        	}
        	if(!"".equals(a[5])){
        		where=where+" and escaper_type="+a[5];
        	}
        	if(!"".equals(a[6])){
        		where=where+" and convert(input_time,date)>=convert('"+a[6]+"',date)";
        	}
        	if(!"".equals(a[7])){
        		where=where+" and convert(input_time,date)<=convert('"+a[7]+"',date)";
        	}
        }else{
        	where="";
        }
        String sssql1="";
        if(a[0].indexOf("1") != -1){//要查询发布数量
    			sql1=sql1+"left join (select count(1),"+use+" as aaa from  buz_escape_debts where report_state=1050 "+where+" group by aaa) fbcount on org.";
    	}
        if(a[0].indexOf("2") != -1){//要查询发布的融资金额
        	String asql="select aaa,sum(b.rongzi) from "
					+ "(select debt_id,"+use+" as aaa from  buz_escape_debts where report_state=1050 "+where+" group by debt_id,aaa) a"
			        + "left join (select  case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end as rongzi,fdebt_id from  buz_escape_debtInfo group by fdebt_id) b "
			        + "on a.debt_id=b.fdebt_id group by aaa";
        	if("".equals(sssql1)){
    			sssql1=asql;
    		}else{
    			sssql1="select * from ("+sssql1+") a left join ("+asql+") b on a.aaa=b.aaa group by aaa";
    		}
    	}

        String sssql2="";
        if(a[0].indexOf("3") != -1){//要查询撤销数量
        	if("".equals(sssql2)){
    			sssql1="select count(1),"+use+" as aaa from  buz_escape_debts where cancel_state=1040 "+where+" group by aaa";
    		}
    	}
        if(a[0].indexOf("4") != -1){//要查询撤销的融资金额
        	String asql="select aaa,sum(b.rongzi) from "
					+ "(select debt_id,"+use+" as aaa from  buz_escape_debts where cancel_state=1040 "+where+" group by debt_id,aaa) a"
			        + "left join (select  case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end as rongzi,fdebt_id from  buz_escape_debtInfo group by fdebt_id) b "
			        + "on a.debt_id=b.fdebt_id group by aaa";
        	if("".equals(sssql2)){
        		sssql2=asql;
    		}else{
    			sssql2="select * from ("+sssql2+") a left join ("+asql+") b on a.aaa=b.aaa group by aaa";
    		}
    	}
        String sssql3="";
        if(a[0].indexOf("5") != -1){//要查询警示期数量
        	if("".equals(sssql3)){
        		sssql3="select count(1),"+use+" as aaa from  buz_escape_debts where report_state=1010 "+where+" group by aaa";
    		}
    	}
        if(a[0].indexOf("6") != -1){//要查询警示期的融资金额
        	String asql="select aaa,sum(b.rongzi) from "
					+ "(select debt_id,"+use+" as aaa from  buz_escape_debts where report_state=1010 "+where+" group by debt_id,aaa) a"
			        + "left join (select  case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end as rongzi,fdebt_id from  buz_escape_debtInfo group by fdebt_id) b "
			        + "on a.debt_id=b.fdebt_id group by aaa";
        	if("".equals(sssql3)){
        		sssql3=asql;
    		}else{
    			sssql3="select * from ("+sssql3+") a left join ("+asql+") b on a.aaa=b.aaa group by aaa";
    		}
    	}
        String sssql4="";
        if(a[0].indexOf("7") != -1){//要查询警示期撤销数量
        	if("".equals(sssql4)){
        		sssql4="select count(1),"+use+" as aaa from  buz_escape_debts where report_state=1070 "+where+" group by aaa";
    		}
    	}
        if(a[0].indexOf("8") != -1){//要查询警示期撤销的融资金额
        	String asql="select aaa,sum(b.rongzi) from "
					+ "(select debt_id,"+use+" as aaa from  buz_escape_debts where report_state=1070 "+where+" group by debt_id,aaa) a"
			        + "left join (select  case (sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)))>0 when 1 then  sum(CONVERT (break_catl, DECIMAL))+sum(CONVERT (break_intr, DECIMAL)) else 0 end as rongzi,fdebt_id from  buz_escape_debtInfo group by fdebt_id) b "
			        + "on a.debt_id=b.fdebt_id group by aaa";
        	if("".equals(sssql4)){
        		sssql4=asql;
    		}else{
    			sssql4="select * from ("+sssql4+") a left join ("+asql+") b on a.aaa=b.aaa group by aaa";
    		}
    	}
        String sssql="";
        if("".equals(sssql1)){
        	if("".equals(sssql2)){
        		if("".equals(sssql3)){
        			 if("".equals(sssql4)){

        			 }else{
        				 sssql=sssql4;
        			 }
        		}else{
        			if("".equals(sssql4)){
        				sssql=sssql3;
       			    }else{
       				  sssql="select * from ("+sssql3+") a left join ("+sssql4+") b on ";
       			    }
        		}

        	}else{

        	}


        }else{

        }
        StringBuilder listSql = new StringBuilder("select  * from user where 1=1");
        listSql.append(" limit "+pager.getStartRecord()+","+pager.getPageSize());
        String countSql = "select count(1) from user";
        //Object[] params = new Object[]{};
        List<Map<String,Object>> result = this.jdbcTemplate.queryForList(listSql.toString());
        int recordCount = this.jdbcTemplate.queryForObject(countSql, Integer.class);
        //页容量
        int pageSize = pager.getPageSize();
        //页数
        pager.setPageCount(recordCount / pageSize + (recordCount % pageSize > 0 ? 1 : 0));
        //记录数
        pager.setRecordCount(recordCount);
        //记录
        pager.setExhibitDatas(result);
        //成功
        pager.setIsSuccess(true);

        String json = JSON.toJSONString(pager);

        System.out.println("====测试用例====="+json);
        return json;
    }

  //地区下拉框数据
    public   Map<String,String> listareaShuJu(){
        //映射Pager对象
    	//String area_code="330100";
        String listSql = "select dept_code,dept_name from org_dept";
        List<Map<String,Object>> list = this.jdbcTemplate.queryForList(listSql.toString());
        Map<String,String> map = new HashMap<>();
        if(list.size()>0){
        for(int i=0;i<list.size();i++){
        	String code=(String) list.get(i).get("dept_code");
        	String area=(String) list.get(i).get("dept_name");
        	map.put(code, area);
        }
        }
        System.out.println("====测试用例====="+map);
        return map;
    }
}
